{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c918bf33",
   "metadata": {},
   "source": [
    "数据集构建文档"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db4e770a",
   "metadata": {},
   "source": [
    "**本文档旨在构建一个虚拟在线教育数据集**<br>\n",
    "**所有维度、时间、数量、金额、转化率、维度占比等指标或参数均为虚拟设定值，与真实业务数据无关，可以根据需求调整**。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "56c55a03",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy  as np\n",
    "import matplotlib.pyplot as plt\n",
    "import datetime\n",
    "import math"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "668fab07",
   "metadata": {},
   "source": [
    "# 维度表-看板字段表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "bbfe3fa9",
   "metadata": {},
   "outputs": [],
   "source": [
    "cate_L1L2 = pd.read_excel('看板字段表.xlsx',sheet_name=\"渠道\")\n",
    "# cate_grade = pd.read_excel('看板字段表.xlsx',sheet_name=\"年级\")\n",
    "cate_grade = pd.read_excel('看板字段表.xlsx',sheet_name=\"人群\")\n",
    "cate_city = pd.read_excel('看板字段表.xlsx',sheet_name=\"城市\")\n",
    "cate_sex = pd.read_excel('看板字段表.xlsx',sheet_name=\"性别\")\n",
    "cate_lesson = pd.read_excel('看板字段表.xlsx',sheet_name=\"课程\")\n",
    "cate_salesman = pd.read_excel('看板字段表.xlsx',sheet_name=\"销售\")\n",
    "cate_register = pd.read_excel('看板字段表.xlsx',sheet_name=\"注册\")\n",
    "cate_refund = pd.read_excel('看板字段表.xlsx',sheet_name=\"退款\")\n",
    "cate_activitydate = pd.read_excel('看板字段表.xlsx',sheet_name=\"活动日期表\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9ee94cb",
   "metadata": {},
   "source": [
    "# 事实表-用户表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "18503c99",
   "metadata": {},
   "outputs": [],
   "source": [
    "year = 2077\n",
    "size = 100000 #数据集大小\n",
    "base_date =  np.datetime64(str(year-1)+ \"-12-31\") #以2077-2079年数据构建"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3205b593",
   "metadata": {},
   "outputs": [],
   "source": [
    "user = pd.DataFrame({\"创建时间\":[datetime.datetime.now()] * size})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e796cb95",
   "metadata": {},
   "outputs": [],
   "source": [
    "user['用户ID'] = np.arange(10000,10000 +len(user)) \n",
    "user[\"用户ID\"] = \"U\" + user['用户ID'] .astype(str)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f054dc6f",
   "metadata": {},
   "source": [
    "## 用户表分类数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0a7c369d",
   "metadata": {},
   "outputs": [],
   "source": [
    "rs = np.random.RandomState(year)\n",
    "user[\"渠道ID\"] = rs.choice(cate_L1L2[\"渠道ID\"],user.shape[0],p=cate_L1L2[\"P\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9de62142",
   "metadata": {},
   "outputs": [],
   "source": [
    "paidfeed = cate_L1L2.set_index(\"渠道ID\")[\"付费流量占比\"].to_dict()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20699296",
   "metadata": {},
   "source": [
    "根据渠道分配付费免费流量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4235f796",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "按渠道匹配流量类型： channel01 →付费流量占比 0.48\n",
      "按渠道匹配流量类型： channel02 →付费流量占比 0.29\n",
      "按渠道匹配流量类型： channel03 →付费流量占比 0.55\n",
      "按渠道匹配流量类型： channel04 →付费流量占比 0.31\n",
      "按渠道匹配流量类型： channel05 →付费流量占比 0.71\n",
      "按渠道匹配流量类型： channel06 →付费流量占比 0.3\n",
      "按渠道匹配流量类型： channel07 →付费流量占比 0.7\n",
      "按渠道匹配流量类型： channel08 →付费流量占比 0.71\n",
      "按渠道匹配流量类型： channel09 →付费流量占比 0.53\n",
      "按渠道匹配流量类型： channel10 →付费流量占比 0.48\n",
      "按渠道匹配流量类型： channel11 →付费流量占比 0.68\n",
      "按渠道匹配流量类型： channel12 →付费流量占比 0.35\n",
      "按渠道匹配流量类型： channel13 →付费流量占比 0.7\n",
      "按渠道匹配流量类型： channel14 →付费流量占比 0.4\n",
      "按渠道匹配流量类型： channel15 →付费流量占比 0.28\n",
      "按渠道匹配流量类型： channel16 →付费流量占比 0.49\n",
      "按渠道匹配流量类型： channel17 →付费流量占比 0.55\n",
      "按渠道匹配流量类型： channel18 →付费流量占比 0.64\n",
      "按渠道匹配流量类型： channel19 →付费流量占比 0.59\n",
      "按渠道匹配流量类型： channel20 →付费流量占比 0.57\n",
      "按渠道匹配流量类型： channel21 →付费流量占比 0.41\n",
      "按渠道匹配流量类型： channel22 →付费流量占比 0.46\n",
      "按渠道匹配流量类型： channel23 →付费流量占比 0.56\n",
      "按渠道匹配流量类型： channel24 →付费流量占比 0.49\n",
      "按渠道匹配流量类型： channel25 →付费流量占比 0.32\n",
      "按渠道匹配流量类型： channel26 →付费流量占比 0.6\n",
      "按渠道匹配流量类型： channel27 →付费流量占比 0.6\n",
      "按渠道匹配流量类型： channel28 →付费流量占比 0.53\n",
      "按渠道匹配流量类型： channel29 →付费流量占比 0.52\n",
      "按渠道匹配流量类型： channel30 →付费流量占比 0.4\n"
     ]
    }
   ],
   "source": [
    "for each in paidfeed:\n",
    "    paidpect = paidfeed[each]\n",
    "    print(\"按渠道匹配流量类型：\",each,\"→付费流量占比\",paidfeed[each])\n",
    "    rs = np.random.RandomState(int(paidfeed[each]))\n",
    "    user.loc[user[\"渠道ID\"] == each ,\"流量类型\"] = rs.choice([\"付费流量\",\"免费流量\"],len( user.loc[user[\"渠道ID\"] == each ,\"渠道ID\"]),p=[paidpect,1-paidpect])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "afea7134",
   "metadata": {},
   "outputs": [],
   "source": [
    "rs = np.random.RandomState(year*2)\n",
    "user[\"性别\"] = rs.choice(cate_sex[\"性别\"],user.shape[0],p=cate_sex[\"P\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "72009b98",
   "metadata": {},
   "outputs": [],
   "source": [
    "rs = np.random.RandomState(year*4)\n",
    "user[\"城市\"] = rs.choice(cate_city[\"城市\"],user.shape[0],p=cate_city[\"P\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "902a178f",
   "metadata": {},
   "outputs": [],
   "source": [
    "user = pd.merge(pd.merge(user,cate_L1L2[[\"渠道ID\",\"渠道类别\"]],on=\"渠道ID\"),cate_city[[\"城市\",\"城市分级\"]],on=\"城市\").sort_values(\"用户ID\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99f123a4",
   "metadata": {},
   "source": [
    "按渠道和城市分级随机分配人群"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a91a2e9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = []\n",
    "for each_citylevel,each_channel in user[[\"城市分级\",\"渠道类别\"]].drop_duplicates().values.tolist():\n",
    "    usergroup = user[(user[\"渠道类别\"] == each_channel) & (user[\"城市分级\"] == each_citylevel)].copy()\n",
    "    people_p = cate_grade[\"F\"] * np.random.choice(np.arange(0.6,1.5,0.01),len(cate_grade))\n",
    "    \n",
    "    usergroup[\"人群\"] = np.random.choice(cate_grade[\"人群\"],usergroup.shape[0],p=people_p/people_p.sum())\n",
    "    temp.append(usergroup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f8c25c76",
   "metadata": {},
   "outputs": [],
   "source": [
    "user = pd.concat(temp).sort_index().copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d35946b8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# user[\"用户身份\"] = user[\"学习阶段\"].map(cate_grade.set_index(\"学习阶段\")[\"用户身份\"].to_dict())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f53948f6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "按人群随机设定年龄： 小镇青年 → ['18', '28']\n",
      "按人群随机设定年龄： Z世代 → ['16', '26']\n",
      "按人群随机设定年龄： 新锐白领 → ['23', '35']\n",
      "按人群随机设定年龄： 精致爸妈 → ['26', '36']\n",
      "按人群随机设定年龄： 都市蓝领 → ['29', '50']\n",
      "按人群随机设定年龄： 资深中产 → ['35', '50']\n",
      "按人群随机设定年龄： 小镇中老年 → ['40', '65']\n",
      "按人群随机设定年龄： 都市银发 → ['50', '65']\n"
     ]
    }
   ],
   "source": [
    "grade_age = cate_grade.set_index(\"人群\")[\"年龄\"].str.split(\",\").to_dict()\n",
    "for each in grade_age:\n",
    "    print(\"按人群随机设定年龄：\",each,\"→\",grade_age[each])\n",
    "    rs = np.random.RandomState(int(grade_age[each][0]))\n",
    "    user.loc[user[\"人群\"] == each ,\"年龄\"] = rs.randint(int(grade_age[each][0]),int(grade_age[each][1]),len( user.loc[user[\"人群\"] == each ,\"人群\"]))\n",
    "user[\"年龄\"] = user[\"年龄\"].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "6676bf12",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[15, 20, 25, 30, 35, 40, 45, 50, inf]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agebins =  [*range(15,55,5)] +[np.inf]\n",
    "agebins"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "7453d8cd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['15~20岁', '20~25岁', '25~30岁', '30~35岁', '35~40岁', '40~45岁', '45~50岁', '50岁以上']"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agelabels = [\"{}~{}岁\".format(*i) for i in  [*zip([*range(15,55,5)],[*range(15,55,5)][1:])]] + [\"50岁以上\"]\n",
    "agelabels "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "8a85b926",
   "metadata": {},
   "outputs": [],
   "source": [
    "user[\"年龄段\"] = pd.cut(user[\"年龄\"],bins =agebins, labels=agelabels)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67791f06",
   "metadata": {},
   "source": [
    "## 用户表时间数据"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f345491",
   "metadata": {},
   "source": [
    "注册"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "fdc7bd6c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# user.drop(columns=['注册时间',\"约课时间\",\"试课时间\",\"购买时间\",\"复购时间\",\"分享时间\"],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "eac5992c",
   "metadata": {},
   "outputs": [],
   "source": [
    "user = user.sort_values('用户ID')\n",
    "rs = np.random.RandomState(year*5)\n",
    "user[\"注册时间\"] = np.sort(rs.choice(cate_register[\"数字\"],p=cate_register[\"P\"],size=len(user)) + base_date)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "869a3e87",
   "metadata": {},
   "source": [
    "根据渠道和城市分级随机一个转化区分权重"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "88405a8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "diffby = user[\"渠道类别\"].drop_duplicates().tolist()\n",
    "# rs = np.random.RandomState(len(diffby))\n",
    "diff_p_channel = dict(zip(diffby,rs.choice(np.arange(0.9,1.2,0.01),len(diffby),replace=False))) # 转化周期区分\n",
    "diff_t_channel = dict(zip(diffby,rs.choice(np.arange(0.8,1.2,0.05),len(diffby),replace=False))) # 转化率区分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "68c38223",
   "metadata": {},
   "outputs": [],
   "source": [
    "diffby = user[\"城市分级\"].drop_duplicates().tolist()\n",
    "# rs = np.random.RandomState(len(diffby))\n",
    "diff_p_geo = dict(zip(diffby,rs.choice(np.arange(0.9,1.2,0.01),len(diffby),replace=False)))# 转化周期区分\n",
    "diff_t_geo = dict(zip(diffby,rs.choice(np.arange(0.8,1.1,0.05),len(diffby),replace=False)))# 转化率区分"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "408e56c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "diffby = user[[\"渠道类别\",\"城市分级\"]].drop_duplicates().copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "4bc64686",
   "metadata": {},
   "outputs": [],
   "source": [
    "diffby[\"diff_p_rate\"] = diffby[\"渠道类别\"].map(diff_p_channel) * diffby[\"城市分级\"].map(diff_p_geo)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "90f195ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "diffby[\"diff_t_rate\"] = diffby[\"渠道类别\"].map(diff_t_channel) * diffby[\"城市分级\"].map(diff_t_geo)"
   ]
  },
  {
   "cell_type": "raw",
   "id": "0664b5f5",
   "metadata": {},
   "source": [
    "转化率随机方式：随机选取trans_rate的转化率，假定转化率每月均匀增长\n",
    "转化周期随机方式：利用偏态分布gamma分布模拟模拟转化间隔\n",
    "转化率设定值：100注册-50约课-30试课-10购买-5复购-7分享\n",
    "转化周期设定值：注册-1约课-1试课-2购买-30复购-10分享"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec21d291",
   "metadata": {},
   "source": [
    "转化率在此基础上上下波动"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "602f4137",
   "metadata": {},
   "outputs": [],
   "source": [
    "trans_rate = {\n",
    "    \"注册-约课\":np.arange(0.6,0.65,0.001),\n",
    "    \"约课-试课\":np.arange(0.5,0.55,0.001),\n",
    "    \"试课-购买\":np.arange(0.4,0.45,0.001),\n",
    "    \"购买-复购\":np.arange(0.4,0.45,0.001),\n",
    "    \"购买-分享\":np.arange(0.5,0.55,0.001),\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef1e0a64",
   "metadata": {},
   "source": [
    "转化周期在此基础上上下波动"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "fc3a8a1d",
   "metadata": {},
   "outputs": [],
   "source": [
    "trans_period = {\n",
    "    \"注册-约课\":np.arange(0.8,1.5,0.001),\n",
    "    \"约课-试课\":np.arange(0.9,1.6,0.001),\n",
    "    \"试课-购买\":np.arange(1.8,2.5,0.001),\n",
    "    \"购买-复购\":np.arange(35,45,0.001),\n",
    "    \"购买-分享\":np.arange(8,15,0.001),\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "305f9daf",
   "metadata": {},
   "source": [
    "假定转化率按月均匀增加，最后一个月相对第一个月增长10%"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "2e025d07",
   "metadata": {},
   "outputs": [],
   "source": [
    "user[\"ym\"] = user[\"注册时间\"].dt.year *100 + user[\"注册时间\"].dt.quarter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "7961227a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "第1组,第2组,第3组,第4组,第5组,第6组,第7组,第8组,第9组,第10组,第11组,第12组,第13组,第14组,第15组,第16组,第17组,第18组,第19组,第20组,第21组,第22组,第23组,第24组,第25组,第26组,第27组,第28组,第29组,第30组,第31组,第32组,第33组,第34组,第35组,第36组,第37组,第38组,第39组,第40组,第41组,第42组,"
     ]
    }
   ],
   "source": [
    "n = 1\n",
    "usergrouplist = []\n",
    "for each_group in diffby.values.tolist():\n",
    "    print(\"第\" + str(n) + \"组\",end=\",\")\n",
    "    usergroup = user[(user[\"渠道类别\"]==each_group[0]) & (user[\"城市分级\"]==each_group[1])].copy()\n",
    "    diff_p = each_group[2]\n",
    "    diff_t = each_group[3]\n",
    "    ym = (usergroup[\"注册时间\"].dt.year *100 + usergroup[\"注册时间\"].dt.quarter).drop_duplicates().sort_values().tolist()\n",
    "    growth_rate = np.linspace(1,1.1,len(ym))\n",
    "    rs = np.random.RandomState(year*66) # 随机项\n",
    "    growth_rate = rs.normal(growth_rate,growth_rate*0.01) # 增加一个正态分布扰动\n",
    "    for each_ym,growth in [*zip(ym,growth_rate)]: # 假定转化率均匀增长\n",
    "#         print(\"根据转化率生成转化时间数据：{}，转化率增长率：{}。\".format(each_ym,growth),end=)\n",
    "\n",
    "        each_ym_index = usergroup[usergroup[\"ym\"] == each_ym].index\n",
    "\n",
    "        # 约课\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        trans_p = rs.choice(trans_period[\"注册-约课\"])\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        randint = np.around(rs.gamma(trans_p*diff_p,1,len(each_ym_index)))\n",
    "        usergroup.loc[each_ym_index,\"约课时间\"] = usergroup.loc[each_ym_index,\"注册时间\"] + pd.to_timedelta(randint.astype('int32'),unit=\"d\")\n",
    "\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        trans = rs.choice(trans_rate[\"注册-约课\"]) * growth * diff_t# 转化率 * 增长率\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        nan_index = rs.choice(each_ym_index,math.ceil(len(each_ym_index) * (1-trans)),replace=False) \n",
    "        usergroup.loc[nan_index,\"约课时间\"] = pd.NaT\n",
    "\n",
    "        # 试课\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        trans_p = rs.choice(trans_period[\"约课-试课\"])\n",
    "        next_trans_index = usergroup[usergroup[\"约课时间\"].notnull()].index.intersection(each_ym_index) # 月份循环索引和值非空索引取交集\n",
    "        rs = np.random.RandomState(each_ym) \n",
    "        randint = np.around(rs.gamma(trans_p*diff_p,1,len(next_trans_index)))\n",
    "        usergroup.loc[next_trans_index,\"试课时间\"] = usergroup.loc[next_trans_index,\"约课时间\"] + pd.to_timedelta(randint.astype('int32'),unit=\"d\")\n",
    "\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        trans = rs.choice(trans_rate[\"约课-试课\"]) * growth * diff_t # 转化率 * 增长率\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        nan_index = rs.choice(next_trans_index,math.ceil(len(next_trans_index) * (1-trans )),replace=False)\n",
    "        usergroup.loc[nan_index,\"试课时间\"] = pd.NaT\n",
    "\n",
    "        # 购买\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        trans_p = rs.choice(trans_period[\"试课-购买\"])\n",
    "        next_trans_index = usergroup[usergroup[\"试课时间\"].notnull()].index.intersection(each_ym_index) # 月份循环索引和值非空索引取交集\n",
    "        rs = np.random.RandomState(each_ym) \n",
    "        randint = np.around(rs.gamma(trans_p*diff_p,1,len(next_trans_index)))\n",
    "        usergroup.loc[next_trans_index,\"购买时间\"] = usergroup.loc[next_trans_index,\"试课时间\"] + pd.to_timedelta(randint.astype('int32'),unit=\"d\")\n",
    "\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        trans = rs.choice(trans_rate[\"试课-购买\"]) * growth * diff_t # 转化率 * 增长率\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        nan_index = rs.choice(next_trans_index,math.ceil(len(next_trans_index) * (1-trans )),replace=False)\n",
    "        usergroup.loc[nan_index,\"购买时间\"] = pd.NaT\n",
    "\n",
    "        # 复购\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        trans_p = rs.choice(trans_period[\"购买-复购\"])\n",
    "        next_trans_index = usergroup[usergroup[\"购买时间\"].notnull()].index.intersection(each_ym_index) # 月份循环索引和值非空索引取交集\n",
    "        rs = np.random.RandomState(each_ym) \n",
    "        randint = np.around(rs.gamma(trans_p*diff_p,1.5,len(next_trans_index)))\n",
    "        usergroup.loc[next_trans_index,\"复购时间\"] = usergroup.loc[next_trans_index,\"购买时间\"] + pd.to_timedelta(randint.astype('int32'),unit=\"d\")\n",
    "\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        trans = rs.choice(trans_rate[\"购买-复购\"]) * growth * diff_t # 转化率 * 增长率\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        nan_index = rs.choice(next_trans_index,math.ceil(len(next_trans_index) * (1-trans )),replace=False)\n",
    "        usergroup.loc[nan_index,\"复购时间\"] = pd.NaT\n",
    "\n",
    "        # 分享\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        trans_p = rs.choice(trans_period[\"购买-分享\"])  * growth * diff_t\n",
    "        next_trans_index = usergroup[usergroup[\"购买时间\"].notnull()].index.intersection(each_ym_index) # 月份循环索引和值非空索引取交集\n",
    "        rs = np.random.RandomState(each_ym) \n",
    "        randint = np.around(rs.gamma(trans_p*diff_p,1,len(next_trans_index)))\n",
    "        usergroup.loc[next_trans_index,\"分享时间\"] = usergroup.loc[next_trans_index,\"购买时间\"] + pd.to_timedelta(randint.astype('int32'),unit=\"d\")\n",
    "\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        trans = rs.choice(trans_rate[\"购买-分享\"]) * growth # 转化率 * 增长率\n",
    "        rs = np.random.RandomState(each_ym)\n",
    "        nan_index = rs.choice(next_trans_index,math.ceil(len(next_trans_index) * (1-trans )),replace=False)\n",
    "        usergroup.loc[nan_index,\"分享时间\"] = pd.NaT\n",
    "    usergrouplist.append(usergroup.copy())\n",
    "    n += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "5b1d6018",
   "metadata": {},
   "outputs": [],
   "source": [
    "user = pd.concat(usergrouplist).sort_values('用户ID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "54da2db1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# user[['注册时间',\"约课时间\",\"试课时间\",\"购买时间\",\"复购时间\",\"分享时间\"]].to_clipboard()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7c707bfb",
   "metadata": {},
   "source": [
    "# 事实表-投放费用表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "04371faa",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "渠道ID       注册时间      \n",
       "channel01  2077-01-01    2\n",
       "           2077-01-02    5\n",
       "           2077-01-03    6\n",
       "           2077-01-04    3\n",
       "           2077-01-05    4\n",
       "                        ..\n",
       "channel30  2079-12-25    1\n",
       "           2079-12-27    3\n",
       "           2079-12-28    1\n",
       "           2079-12-30    2\n",
       "           2079-12-31    2\n",
       "Name: 用户ID, Length: 29921, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user.groupby([\"渠道ID\",\"注册时间\"])[\"用户ID\"].count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "4dc28484",
   "metadata": {},
   "outputs": [],
   "source": [
    "ad = user.groupby([\"渠道ID\",\"注册时间\"])[\"用户ID\"].count().rename(\"注册用户数\").reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "987d1190",
   "metadata": {},
   "outputs": [],
   "source": [
    "ad = ad.set_index(\"注册时间\").groupby(\"渠道ID\").rolling(30,min_periods=1).mean().rename(columns={\"注册用户数\":\"注册用户数MA30\"}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "c3ee481d",
   "metadata": {},
   "outputs": [],
   "source": [
    "ad = pd.merge(ad,cate_L1L2[[\"渠道ID\",\"渠道类别\",\"渠道名称\",\"CAC\"]],right_on = \"渠道ID\", left_on = \"渠道ID\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "f536751f",
   "metadata": {},
   "outputs": [],
   "source": [
    "adcost = ad[\"CAC\"] * ad[\"注册用户数MA30\"] *  (ad[\"注册时间\"] - base_date).dt.days.rpow(1.001) # 按每日0.01%增长"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "94534687",
   "metadata": {},
   "outputs": [],
   "source": [
    "rs = np.random.RandomState(year*99) # 随机项\n",
    "ad[\"投放费用\"] = rs.normal(adcost,adcost*0.05).astype(int) #控制波动在设定投放费用的5%的三个标准差范围内"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "7fc4988a",
   "metadata": {},
   "outputs": [],
   "source": [
    "ad.rename(columns={\"注册时间\":\"投放时间\"},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "1a07076f",
   "metadata": {},
   "outputs": [],
   "source": [
    "ad[\"投放费用\"] = ad[\"投放费用\"] * ad[\"投放时间\"].isin(cate_activitydate[\"活动时间\"]).apply(lambda x: 1.2 if x == True else 1) # 活动时间增加投放费用"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d63abc67",
   "metadata": {},
   "source": [
    "# 事实表-AB实验记录表"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "348787c8",
   "metadata": {},
   "source": [
    "由于数据小，不对总体再抽样（实际中应该取小部分流量）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "c86216af",
   "metadata": {},
   "outputs": [],
   "source": [
    "test1_index = user[user[\"注册时间\"].dt.year*100+user[\"注册时间\"].dt.month == year*100+7].index  # 第一年的7月\n",
    "test1 = pd.DataFrame({\"用户ID\":user.loc[test1_index,\"用户ID\"],\n",
    "                      \"进组时间\":user.loc[test1_index,\"注册时间\"],\n",
    "                      \"行为时间\":user.loc[test1_index,\"约课时间\"],\n",
    "                      \"实验名称\":\"课程推荐算法迭代\"})\n",
    "\n",
    "\n",
    "test2_index = user[user[\"试课时间\"].dt.year*100+user[\"试课时间\"].dt.month == year*100+107].index  # 第二年的7月\n",
    "test2 = pd.DataFrame({\"用户ID\":user.loc[test2_index,\"用户ID\"],\n",
    "                      \"进组时间\":user.loc[test2_index,\"试课时间\"],\n",
    "                      \"行为时间\":user.loc[test2_index,\"购买时间\"],\n",
    "                      \"实验名称\":\"试课教学体验优化\"})\n",
    "\n",
    "test3_index = user[user[\"购买时间\"].dt.year*100+user[\"购买时间\"].dt.month == year*100+207].index  # 第三年的7月\n",
    "test3 = pd.DataFrame({\"用户ID\":user.loc[test3_index,\"用户ID\"],\n",
    "                      \"进组时间\":user.loc[test3_index,\"购买时间\"],\n",
    "                      \"行为时间\":user.loc[test3_index,\"分享时间\"],\n",
    "                      \"实验名称\":\"分享页面改版测试\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "0d1e9a7e",
   "metadata": {},
   "outputs": [],
   "source": [
    "ABTEST = pd.concat([test1,test2,test3]).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "1bff0b10",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户ID</th>\n",
       "      <th>进组时间</th>\n",
       "      <th>行为时间</th>\n",
       "      <th>实验名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>U16325</td>\n",
       "      <td>2077-07-01</td>\n",
       "      <td>2077-07-01</td>\n",
       "      <td>课程推荐算法迭代</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>U16326</td>\n",
       "      <td>2077-07-01</td>\n",
       "      <td>2077-07-02</td>\n",
       "      <td>课程推荐算法迭代</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>U16327</td>\n",
       "      <td>2077-07-01</td>\n",
       "      <td>2077-07-02</td>\n",
       "      <td>课程推荐算法迭代</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>U16328</td>\n",
       "      <td>2077-07-01</td>\n",
       "      <td>2077-07-02</td>\n",
       "      <td>课程推荐算法迭代</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>U16329</td>\n",
       "      <td>2077-07-01</td>\n",
       "      <td>2077-07-01</td>\n",
       "      <td>课程推荐算法迭代</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3922</th>\n",
       "      <td>U85601</td>\n",
       "      <td>2079-07-30</td>\n",
       "      <td>2079-08-11</td>\n",
       "      <td>分享页面改版测试</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3923</th>\n",
       "      <td>U85632</td>\n",
       "      <td>2079-07-30</td>\n",
       "      <td>2079-08-04</td>\n",
       "      <td>分享页面改版测试</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3924</th>\n",
       "      <td>U85638</td>\n",
       "      <td>2079-07-31</td>\n",
       "      <td>NaT</td>\n",
       "      <td>分享页面改版测试</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3925</th>\n",
       "      <td>U85654</td>\n",
       "      <td>2079-07-31</td>\n",
       "      <td>2079-08-18</td>\n",
       "      <td>分享页面改版测试</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3926</th>\n",
       "      <td>U85821</td>\n",
       "      <td>2079-07-31</td>\n",
       "      <td>2079-08-09</td>\n",
       "      <td>分享页面改版测试</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3927 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        用户ID       进组时间       行为时间      实验名称\n",
       "0     U16325 2077-07-01 2077-07-01  课程推荐算法迭代\n",
       "1     U16326 2077-07-01 2077-07-02  课程推荐算法迭代\n",
       "2     U16327 2077-07-01 2077-07-02  课程推荐算法迭代\n",
       "3     U16328 2077-07-01 2077-07-02  课程推荐算法迭代\n",
       "4     U16329 2077-07-01 2077-07-01  课程推荐算法迭代\n",
       "...      ...        ...        ...       ...\n",
       "3922  U85601 2079-07-30 2079-08-11  分享页面改版测试\n",
       "3923  U85632 2079-07-30 2079-08-04  分享页面改版测试\n",
       "3924  U85638 2079-07-31        NaT  分享页面改版测试\n",
       "3925  U85654 2079-07-31 2079-08-18  分享页面改版测试\n",
       "3926  U85821 2079-07-31 2079-08-09  分享页面改版测试\n",
       "\n",
       "[3927 rows x 4 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ABTEST"
   ]
  },
  {
   "cell_type": "raw",
   "id": "5f89e945",
   "metadata": {},
   "source": [
    "通过虚构的AB实验提升比例随机生成实验结果\n",
    "a 进组数；b 行为数；c 实验组用户占比；x 对照组转化率；y 提升比例；x(1+y) 实验组转化率\n",
    "在均匀分组的情况下：\n",
    "acx + a(1-c)x(1+y) = b\n",
    "=>x = b/(a*c+a*(1-c)*(1+y))\n",
    "实验组在发生行为用户中抽取的比例 behavior_p = ca*x(1+y)/b \n",
    "实验组在不发生行为用户中抽取的比例notbehavior_p = (c*a-b*behavior_p) / (a-b) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "7194c127",
   "metadata": {},
   "outputs": [],
   "source": [
    "testname = ABTEST[\"实验名称\"].drop_duplicates().tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "d56936c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = []\n",
    "for eachtest in list(zip(testname,(0.2,-0.25,-0.01),(0.5,0.35,0.65))):\n",
    "    test = ABTEST.loc[ABTEST[\"实验名称\"]==eachtest[0]].copy()\n",
    "    c=eachtest[2]\n",
    "    b = test[\"行为时间\"].notna().sum()\n",
    "    a = test[\"进组时间\"].count()\n",
    "    y =eachtest[1]\n",
    "    x = b/(a*c+a*(1-c)*(1+y))\n",
    "    behavior_p = c*a*x*(1+y)/b\n",
    "    notbehavior_p = (c*a-b*behavior_p) / (a-b) \n",
    "    rs = np.random.RandomState(a)\n",
    "    test.loc[test[\"行为时间\"].notna(),\"实验组别\"] =  rs.choice([\"实验组\",\"对照组\"],p=[behavior_p ,1-behavior_p ],size=b)\n",
    "    test.loc[test[\"行为时间\"].isna(),\"实验组别\"] =  rs.choice([\"实验组\",\"对照组\"],p=[notbehavior_p,1-notbehavior_p],size=a-b)\n",
    "    temp.append(test.copy())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "a872b507",
   "metadata": {},
   "outputs": [],
   "source": [
    "ABTEST = pd.concat(temp).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2abaf514",
   "metadata": {},
   "source": [
    "# 事实表-用户行为表"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78b748de",
   "metadata": {},
   "source": [
    "简便操作，只添加用户ID、行为时间和行为三个字段，维度数据不进行冗余"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1391f5c6",
   "metadata": {},
   "source": [
    "## 关键行为"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f72e7e6",
   "metadata": {},
   "source": [
    "假设关键行为发生当天及随后2周内会发生随机设定的一系列行为。关键行为：注册完成、约课完成、试课完成、购买完成、复购完成、分享完成。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "47a6de84",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "关键行为： 注册时间\n",
      "关键行为： 约课时间\n",
      "关键行为： 试课时间\n",
      "关键行为： 购买时间\n",
      "关键行为： 复购时间\n",
      "关键行为： 分享时间\n"
     ]
    }
   ],
   "source": [
    "behavior_list = []\n",
    "for each_behavior in ['注册时间','约课时间', '试课时间', '购买时间', '复购时间', '分享时间']:\n",
    "    print(\"关键行为：\",each_behavior)\n",
    "    temp = user[[\"用户ID\",each_behavior]].dropna()\n",
    "    temp[\"行为\"] = each_behavior[:2] + \"完成\"\n",
    "    temp.columns = [\"用户ID\",\"行为时间\",'行为']\n",
    "    behavior_list.append(temp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "c3a4f4f0",
   "metadata": {},
   "outputs": [],
   "source": [
    "behavior = pd.concat(behavior_list).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "100d9f14",
   "metadata": {},
   "source": [
    "关键行为随机重复"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "f1bec7bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "para_behavior = [('约课完成',1,0.5,[0,1,2],[0.7,0.2,0.1]),('试课完成',2,0.5,[0,1,2],[0.7,0.2,0.1]), ('复购完成',50,10,[0,1,2,3],[0.7,0.2,0.07,0.03]), ('分享完成',10,2,[0,1,2],[0.7,0.2,0.1])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "42f3b1c9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "约课完成 1 0.5 [0, 1, 2] [0.7, 0.2, 0.1]\n",
      "试课完成 2 0.5 [0, 1, 2] [0.7, 0.2, 0.1]\n",
      "复购完成 50 10 [0, 1, 2, 3] [0.7, 0.2, 0.07, 0.03]\n",
      "分享完成 10 2 [0, 1, 2] [0.7, 0.2, 0.1]\n"
     ]
    }
   ],
   "source": [
    "for each_behavior,a,b,c,d in para_behavior:\n",
    "    print(each_behavior,a,b,c,d)\n",
    "    each_behavior_index = behavior[behavior[\"行为\"] == each_behavior].index\n",
    "    behavior.loc[each_behavior_index,\"行为重复\"] = behavior.loc[each_behavior_index,\"行为时间\"].apply(\n",
    "                                            lambda x: x + pd.to_timedelta(np.around(rs.normal(a,b,np.random.choice(c,p=d))\n",
    "                                                                                   ).astype('int32'),unit=\"d\")\n",
    "                                            )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "2cbb0110",
   "metadata": {},
   "outputs": [],
   "source": [
    "behavior_repeat_index = behavior[behavior[\"行为重复\"].notna()][\"行为重复\"].apply(lambda x:len(x))[lambda x:x>0].index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "9462c3ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "behavior_repeat = behavior.loc[behavior_repeat_index].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "f71036ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = []\n",
    "for each in para_behavior:\n",
    "    behavior_repeat_each = pd.DataFrame({\"用户ID\":np.repeat(behavior_repeat[\"用户ID\"].values,list(map(len,behavior_repeat[\"行为重复\"].values))),\n",
    "                                   \"行为\":each[0],\n",
    "                                  \"行为时间\":np.concatenate(behavior_repeat[\"行为重复\"].values)})# 参考 https://www.cjavapy.com/article/237/\n",
    "    temp.append(behavior_repeat_each.copy())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "5948a3b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "behavior = pd.concat([pd.concat(temp),behavior[[\"用户ID\",\t\"行为\",\t\"行为时间\"]]]).sort_values([\"用户ID\",\"行为时间\"]).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "b56ae42a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     2077-01-01\n",
       "1     2077-01-02\n",
       "2     2077-01-03\n",
       "3     2077-01-04\n",
       "4     2077-01-05\n",
       "         ...    \n",
       "244   2079-12-08\n",
       "245   2079-12-09\n",
       "246   2079-12-10\n",
       "247   2079-12-11\n",
       "248   2079-12-12\n",
       "Name: 活动时间, Length: 249, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cate_activitydate[\"活动时间\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "2c3b8ab1",
   "metadata": {},
   "outputs": [],
   "source": [
    "behavior_for_activity = behavior[(behavior[\"行为\"] == \"复购完成\") & behavior[\"行为时间\"].isin(cate_activitydate[\"活动时间\"])].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "639ee4ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "behavior_for_activity[\"复购重复\"] = behavior_for_activity[\"行为时间\"].apply(lambda x: x + pd.to_timedelta(np.around(rs.normal(2,0.05,np.random.choice([0,1,2],p=[0.3,0.4,0.3]))\n",
    "                                                                                   ).astype('int32'),unit=\"d\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "581c6908",
   "metadata": {},
   "outputs": [],
   "source": [
    "behavior_for_activity_repeat = pd.DataFrame({\"用户ID\":np.repeat(behavior_for_activity[\"用户ID\"].values,list(map(len,behavior_for_activity[\"复购重复\"].values))),\n",
    "                                   \"行为\":\"复购完成\",\n",
    "                                  \"行为时间\":np.concatenate(behavior_for_activity[\"复购重复\"].values)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "0ac131a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "behavior = pd.concat([behavior,behavior_for_activity_repeat]).sort_values([\"用户ID\",\"行为时间\"]).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ba74ea54",
   "metadata": {},
   "source": [
    "## 登录行为"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a79aef48",
   "metadata": {},
   "source": [
    "用户使用次数会随着时间推移逐渐减少，故使用指数分布模拟"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "260f4110",
   "metadata": {},
   "outputs": [],
   "source": [
    "behavior[\"yq\"] = (behavior[\"行为时间\"].dt.year *10 + behavior[\"行为时间\"].dt.quarter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "f35d381d",
   "metadata": {},
   "outputs": [],
   "source": [
    "yq = behavior[\"yq\"].drop_duplicates().sort_values().tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0de88fcc",
   "metadata": {},
   "source": [
    "假设登录次数随时间均匀增加"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "97e9c5fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "behavior[\"行为登录\"] = behavior[\"行为时间\"].apply(\n",
    "                                        lambda x: x + pd.to_timedelta(\n",
    "                                        np.around(rs.exponential(10,np.random.choice([1,2,3,4,5,6,7],p=[0.3,0.2,0.15,0.15,0.1,0.06,0.04]))).astype('int32'),unit=\"d\").drop_duplicates())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "b4d458a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "behavior_login = pd.DataFrame({\"用户ID\":np.repeat(behavior[\"用户ID\"].values,list(map(len,behavior[\"行为登录\"].values))),\n",
    "                               \"行为\":\"登录成功\",\n",
    "                              \"行为时间\":np.concatenate(behavior[\"行为登录\"].values)})# 参考 https://www.cjavapy.com/article/237/"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10db6edd",
   "metadata": {},
   "source": [
    "登录后随机1~4次行为"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "b195c23d",
   "metadata": {},
   "outputs": [],
   "source": [
    "rs = np.random.RandomState(year*20)\n",
    "behavior_login[\"登录后动作\"] = behavior_login[\"行为时间\"].apply(lambda x :rs.choice([1,2,3,4],p=[0.6,0.2,0.15,0.05])*[x])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60a0788b",
   "metadata": {},
   "source": [
    "## 随机设定行为"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "822be2e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "behavior_action = pd.DataFrame({\"用户ID\":np.repeat(behavior_login[\"用户ID\"].values,list(map(len,behavior_login[\"登录后动作\"].values))),\n",
    "                               \"行为\":\"\",\n",
    "                              \"行为时间\":np.concatenate(behavior_login[\"登录后动作\"].values)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "6fc02a80",
   "metadata": {},
   "outputs": [],
   "source": [
    "rs = np.random.RandomState(year*110)\n",
    "behavior_action[\"行为\"] = rs.choice(['搜索课程','浏览课程','收藏课程','咨询课程'],p=[0.3,0.4,0.2,0.1],size=len(behavior_action))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "8612886a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户ID</th>\n",
       "      <th>行为</th>\n",
       "      <th>行为时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>U10000</td>\n",
       "      <td>浏览课程</td>\n",
       "      <td>2077-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>U10000</td>\n",
       "      <td>浏览课程</td>\n",
       "      <td>2077-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>U10000</td>\n",
       "      <td>浏览课程</td>\n",
       "      <td>2077-01-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>U10000</td>\n",
       "      <td>搜索课程</td>\n",
       "      <td>2077-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>U10000</td>\n",
       "      <td>咨询课程</td>\n",
       "      <td>2077-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1784886</th>\n",
       "      <td>U99999</td>\n",
       "      <td>搜索课程</td>\n",
       "      <td>2080-01-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1784887</th>\n",
       "      <td>U99999</td>\n",
       "      <td>搜索课程</td>\n",
       "      <td>2080-01-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1784888</th>\n",
       "      <td>U99999</td>\n",
       "      <td>搜索课程</td>\n",
       "      <td>2080-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1784889</th>\n",
       "      <td>U99999</td>\n",
       "      <td>搜索课程</td>\n",
       "      <td>2080-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1784890</th>\n",
       "      <td>U99999</td>\n",
       "      <td>浏览课程</td>\n",
       "      <td>2080-01-07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1784891 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           用户ID    行为       行为时间\n",
       "0        U10000  浏览课程 2077-01-12\n",
       "1        U10000  浏览课程 2077-01-12\n",
       "2        U10000  浏览课程 2077-01-12\n",
       "3        U10000  搜索课程 2077-01-07\n",
       "4        U10000  咨询课程 2077-01-04\n",
       "...         ...   ...        ...\n",
       "1784886  U99999  搜索课程 2080-01-09\n",
       "1784887  U99999  搜索课程 2080-01-09\n",
       "1784888  U99999  搜索课程 2080-01-07\n",
       "1784889  U99999  搜索课程 2080-01-07\n",
       "1784890  U99999  浏览课程 2080-01-07\n",
       "\n",
       "[1784891 rows x 3 columns]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "behavior_action"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "7179cb85",
   "metadata": {},
   "outputs": [],
   "source": [
    "behavior = behavior[[\"用户ID\",\"行为\",'行为时间']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "afbfb15d",
   "metadata": {},
   "outputs": [],
   "source": [
    "behavior_login = behavior_login[[\"用户ID\",\"行为\",'行为时间']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "4c07ca29",
   "metadata": {},
   "outputs": [],
   "source": [
    "behaviors = pd.concat([behavior[[\"用户ID\",\"行为\",'行为时间']],behavior_login[[\"用户ID\",\"行为\",'行为时间']],behavior_action]).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "58787afc",
   "metadata": {},
   "outputs": [],
   "source": [
    "behaviors[\"ym\"] = (behaviors[\"行为时间\"].dt.year *100 + behaviors[\"行为时间\"].dt.month)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "6290d2ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "ym = behaviors[\"ym\"].drop_duplicates().sort_values().tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4e356f2",
   "metadata": {},
   "source": [
    "设定每天具体行为时间"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "6fb23d28",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "年月：207701，权重移动：1。\n",
      "年月：207702，权重移动：2。\n",
      "年月：207703，权重移动：3。\n",
      "年月：207704，权重移动：4。\n",
      "年月：207705，权重移动：5。\n",
      "年月：207706，权重移动：6。\n",
      "年月：207707，权重移动：7。\n",
      "年月：207708，权重移动：8。\n",
      "年月：207709，权重移动：9。\n",
      "年月：207710，权重移动：10。\n",
      "年月：207711，权重移动：11。\n",
      "年月：207712，权重移动：12。\n",
      "年月：207801，权重移动：13。\n",
      "年月：207802，权重移动：14。\n",
      "年月：207803，权重移动：15。\n",
      "年月：207804，权重移动：16。\n",
      "年月：207805，权重移动：17。\n",
      "年月：207806，权重移动：18。\n",
      "年月：207807，权重移动：19。\n",
      "年月：207808，权重移动：20。\n",
      "年月：207809，权重移动：21。\n",
      "年月：207810，权重移动：22。\n",
      "年月：207811，权重移动：23。\n",
      "年月：207812，权重移动：24。\n",
      "年月：207901，权重移动：25。\n",
      "年月：207902，权重移动：26。\n",
      "年月：207903，权重移动：27。\n",
      "年月：207904，权重移动：28。\n",
      "年月：207905，权重移动：29。\n",
      "年月：207906，权重移动：30。\n",
      "年月：207907，权重移动：31。\n",
      "年月：207908，权重移动：32。\n",
      "年月：207909，权重移动：33。\n",
      "年月：207910，权重移动：34。\n",
      "年月：207911，权重移动：35。\n",
      "年月：207912，权重移动：36。\n",
      "年月：208001，权重移动：37。\n",
      "年月：208002，权重移动：38。\n",
      "年月：208003，权重移动：39。\n",
      "年月：208004，权重移动：40。\n",
      "年月：208005，权重移动：41。\n",
      "年月：208006，权重移动：42。\n",
      "年月：208007，权重移动：43。\n"
     ]
    }
   ],
   "source": [
    "for each_ym,centre_moving  in [*zip(ym,range(1,1+len(ym)))]:\n",
    "    print(\"年月：{}，权重移动：{}。\".format(each_ym,centre_moving))\n",
    "    each_ym_index = behaviors[behaviors[\"ym\"] == each_ym].index\n",
    "    rs = np.random.RandomState(each_ym)\n",
    "    centre_time_F = [0.5*(1.1**centre_moving),0.6*(1.12**centre_moving),1*(1.11**centre_moving)] # 高峰时点确定随机概率，带上一个向后增长的指数\n",
    "    active_centre_time = np.random.choice([10,16,20],p = [i / sum(centre_time_F) for i in centre_time_F],size=len(each_ym_index))  # 活跃高峰时点\n",
    "    rand_second = (np.random.normal(np.random.normal(active_centre_time,0.08),1) * 60 * 60).astype(int) # 活跃时间在高峰时点附近正态分布\n",
    "    rand_second = [i if i <=86399 else 86399 for i in rand_second] # 避免超过24小时\n",
    "    behaviors.loc[each_ym_index,\"行为具体时间\"] = behaviors.loc[each_ym_index,\"行为时间\"] + pd.to_timedelta(rand_second,unit='S')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55181465",
   "metadata": {},
   "source": [
    "## 构建订单表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "42f458f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = behavior[behavior[\"行为\"].isin([\"购买完成\",\"复购完成\"])][[\"用户ID\",\"行为\",\"行为时间\"]].rename(columns={\"行为\":\"购买复购\",\"行为时间\":\"付款时间\"}).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "379537be",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales[\"购买复购\"] = sales[\"购买复购\"].map({\"购买完成\":\"首单\",\"复购完成\":\"复购\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "c180e52a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户ID</th>\n",
       "      <th>购买复购</th>\n",
       "      <th>付款时间</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>U10000</td>\n",
       "      <td>复购</td>\n",
       "      <td>2077-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>U10000</td>\n",
       "      <td>复购</td>\n",
       "      <td>2077-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>U100001</td>\n",
       "      <td>复购</td>\n",
       "      <td>2077-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>U100001</td>\n",
       "      <td>复购</td>\n",
       "      <td>2077-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>U100003</td>\n",
       "      <td>首单</td>\n",
       "      <td>2077-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73455</th>\n",
       "      <td>U99994</td>\n",
       "      <td>首单</td>\n",
       "      <td>2080-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73456</th>\n",
       "      <td>U99997</td>\n",
       "      <td>复购</td>\n",
       "      <td>2080-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73457</th>\n",
       "      <td>U99997</td>\n",
       "      <td>首单</td>\n",
       "      <td>2080-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73458</th>\n",
       "      <td>U99997</td>\n",
       "      <td>复购</td>\n",
       "      <td>2080-03-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73459</th>\n",
       "      <td>U99998</td>\n",
       "      <td>首单</td>\n",
       "      <td>2080-01-04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>73460 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          用户ID 购买复购       付款时间\n",
       "0       U10000   复购 2077-01-02\n",
       "1       U10000   复购 2077-01-04\n",
       "2      U100001   复购 2077-01-02\n",
       "3      U100001   复购 2077-01-04\n",
       "4      U100003   首单 2077-01-03\n",
       "...        ...  ...        ...\n",
       "73455   U99994   首单 2080-01-03\n",
       "73456   U99997   复购 2080-01-01\n",
       "73457   U99997   首单 2080-01-02\n",
       "73458   U99997   复购 2080-03-03\n",
       "73459   U99998   首单 2080-01-04\n",
       "\n",
       "[73460 rows x 3 columns]"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71cf1108",
   "metadata": {},
   "source": [
    "# 事实表-订单表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "84b78a01",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales[\"订单ID\"] = np.arange(1000,1000+len(sales))\n",
    "sales[\"订单ID\"] = \"O\"  + sales[\"订单ID\"].astype(str)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "334b154d",
   "metadata": {},
   "source": [
    "## 订单表分类型数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "dd218d16",
   "metadata": {},
   "outputs": [],
   "source": [
    "rs = np.random.RandomState(year*6)\n",
    "sales[\"员工ID\"] = rs.choice(cate_salesman[\"员工ID\"],p=cate_salesman[\"F\"]/cate_salesman[\"F\"].sum(),size=len(sales))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "0438078b",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = pd.merge(sales,user[[\"用户ID\",\"年龄\",\"渠道类别\",\"城市分级\"]],on=\"用户ID\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "1b4c366d",
   "metadata": {},
   "outputs": [],
   "source": [
    "cate_lesson[\"推荐年龄min\"] = cate_lesson[\"推荐年龄\"].str.split(\",\").apply(lambda x:int(x[0]))\n",
    "cate_lesson[\"推荐年龄max\"] = cate_lesson[\"推荐年龄\"].str.split(\",\").apply(lambda x:int(x[1]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "3226f80f",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = pd.DataFrame({\"渠道类别\":cate_L1L2[\"渠道类别\"].drop_duplicates(),\"F\":1})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "9ac0cc09",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp2 = pd.DataFrame({\"课程分类\":cate_lesson[\"课程分类\"].drop_duplicates(),\"F\":1})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "79a46eaa",
   "metadata": {},
   "outputs": [],
   "source": [
    "channel_lesson = pd.merge(temp,temp2,on=\"F\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "0818af6a",
   "metadata": {},
   "outputs": [],
   "source": [
    "channel_lesson[\"F\"] = rs.choice(np.arange(0.5,3,(3-0.5)/len(channel_lesson)),size=len(channel_lesson),replace=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "fbd0a239",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = pd.DataFrame({\"城市分级\":cate_city[\"城市分级\"].drop_duplicates(),\"F\":1})\n",
    "temp2 = pd.DataFrame({\"课程分类\":cate_lesson[\"课程分类\"].drop_duplicates(),\"F\":1})\n",
    "city_lesson = pd.merge(temp,temp2,on=\"F\")\n",
    "city_lesson[\"F\"] = rs.choice(np.arange(0.5,2.5,(2.5-0.5)/len(city_lesson)),size=len(city_lesson),replace=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "b8cf3b68",
   "metadata": {},
   "outputs": [],
   "source": [
    "def rand_lesson(x): #按照推荐年龄来匹配课程，渠道和城市分级给予不同的权重\n",
    "    lesson_in_age = cate_lesson[[\"课程ID\",\"F\",\"复购权重\",\"课程分类\"]][(cate_lesson[\"推荐年龄max\"] >=x[\"年龄\"])&(cate_lesson[\"推荐年龄min\"] <= x[\"年龄\"])].copy()\n",
    "    rs = np.random.RandomState(int(x['订单ID'][1:]))\n",
    "    lesson_in_age[\"F\"] = lesson_in_age[\"F\"] * 1 if x[\"购买复购\"] == \"首单\" else lesson_in_age[\"F\"] * lesson_in_age[\"复购权重\"]\n",
    "    lesson_in_age[\"F\"] = lesson_in_age[\"F\"] * lesson_in_age[\"课程分类\"].map(channel_lesson[channel_lesson[\"渠道类别\"]==x[\"渠道类别\"]].set_index(\"课程分类\")[\"F\"].to_dict())\n",
    "    lesson_in_age[\"F\"] = lesson_in_age[\"F\"] * lesson_in_age[\"课程分类\"].map(city_lesson[city_lesson[\"城市分级\"]==x[\"城市分级\"]].set_index(\"课程分类\")[\"F\"].to_dict())\n",
    "    return rs.choice(lesson_in_age[\"课程ID\"],p=lesson_in_age[\"F\"]/lesson_in_age[\"F\"].sum())\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "d6d811dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales[\"课程ID\"] = sales.apply(rand_lesson,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "0259f502",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales = pd.merge(sales,cate_lesson[['课程ID', '课程分类', '课程名称', '课时数', '价格','成本', '课程类型']],how=\"left\",on=\"课程ID\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dfc0ab8c",
   "metadata": {},
   "source": [
    "## 订单表数值型数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "8598c7e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales['ym'] = sales[\"付款时间\"].dt.year *100 + sales[\"付款时间\"].dt.month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "fd853178",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales_ym = sales['ym'].drop_duplicates().tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4f0babd",
   "metadata": {},
   "source": [
    "随机成本"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "af644499",
   "metadata": {},
   "outputs": [],
   "source": [
    "first_ym = len(sales_ym) // 4\n",
    "second_ym =len(sales_ym) // 4 *2 - first_ym \n",
    "third_ym = len(sales_ym) // 4 *3 - first_ym - second_ym \n",
    "last_ym = len(sales_ym) - first_ym - second_ym - third_ym "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "a44b313d",
   "metadata": {},
   "outputs": [],
   "source": [
    "cost_growth = np.hstack([np.linspace(1,1.8,first_ym ),np.linspace(1.8,2,second_ym),np.linspace(2,1.6,third_ym),np.linspace(1.6,1.8,last_ym )])\n",
    "rs = np.random.RandomState(year*88) # 随机项\n",
    "cost_growth = cost_growth + rs.normal(0,0.02,size=len(sales_ym ))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "3dd0f07f",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales[\"成本\"] = (sales[\"ym\"].map(dict(zip(sales_ym,cost_growth))) * sales[\"成本\"]).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "2f9ffbbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "discount = np.arange(0.8,1,0.01)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "669bd1b5",
   "metadata": {},
   "source": [
    "随机折扣"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "9919c65b",
   "metadata": {},
   "outputs": [],
   "source": [
    "rs = np.random.RandomState(year*8)\n",
    "discount = np.arange(0.8,1,0.01)\n",
    "sales[\"实付款\"] = sales[\"价格\"] * rs.choice(discount,size=len(sales)) # 折扣"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee7f757a",
   "metadata": {},
   "source": [
    "# 事实表-退款记录表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "41539ed9",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales[\"ym\"] = (sales[\"付款时间\"].dt.year *100 + sales[\"付款时间\"].dt.month)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "77b65761",
   "metadata": {},
   "outputs": [],
   "source": [
    "ym = sales[\"ym\"].drop_duplicates().sort_values().tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a907c1f",
   "metadata": {},
   "source": [
    "假定退款率 均值为0.05，标准差0.01"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "287e2a78",
   "metadata": {},
   "outputs": [],
   "source": [
    "rs = np.random.RandomState(year*9)\n",
    "refund_rate = rs.normal(0.05,0.01,len(ym))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "e1d14945",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "年月：207701，随机退款率：0.03373655362506123。\n",
      "年月：207702，随机退款率：0.04364459059356905。\n",
      "年月：207703，随机退款率：0.046580614015409826。\n",
      "年月：207704，随机退款率：0.06598223835507144。\n",
      "年月：207705，随机退款率：0.05714671161444774。\n",
      "年月：207706，随机退款率：0.04409818774502236。\n",
      "年月：207707，随机退款率：0.046993844958857986。\n",
      "年月：207708，随机退款率：0.04317169565570339。\n",
      "年月：207709，随机退款率：0.051634120328222816。\n",
      "年月：207710，随机退款率：0.07065216107317787。\n",
      "年月：207711，随机退款率：0.045322152419397035。\n",
      "年月：207712，随机退款率：0.03667349098425462。\n",
      "年月：207801，随机退款率：0.0483011423836882。\n",
      "年月：207802，随机退款率：0.04593881404093933。\n",
      "年月：207803，随机退款率：0.03515433867114146。\n",
      "年月：207804，随机退款率：0.04386040375913785。\n",
      "年月：207805，随机退款率：0.03755186761982559。\n",
      "年月：207806，随机退款率：0.03743654221260626。\n",
      "年月：207807，随机退款率：0.0394594228295737。\n",
      "年月：207808，随机退款率：0.0406895192777802。\n",
      "年月：207809，随机退款率：0.04367547182586124。\n",
      "年月：207810，随机退款率：0.05010574557955933。\n",
      "年月：207811，随机退款率：0.05381837885992358。\n",
      "年月：207812，随机退款率：0.03916110051026928。\n",
      "年月：207901，随机退款率：0.04685184343420155。\n",
      "年月：207902，随机退款率：0.05300909471462842。\n",
      "年月：207903，随机退款率：0.05958830985892906。\n",
      "年月：207904，随机退款率：0.05320556405096084。\n",
      "年月：207905，随机退款率：0.0459856564980693。\n",
      "年月：207906，随机退款率：0.047391163422361667。\n",
      "年月：207907，随机退款率：0.047010553072184005。\n",
      "年月：207908，随机退款率：0.048512748846395934。\n",
      "年月：207909，随机退款率：0.05410802794346624。\n",
      "年月：207910，随机退款率：0.042689273597436614。\n",
      "年月：207911，随机退款率：0.04550462331197545。\n",
      "年月：207912，随机退款率：0.056237031382207925。\n",
      "年月：208001，随机退款率：0.054983422623167105。\n",
      "年月：208002，随机退款率：0.03626890363407668。\n",
      "年月：208003，随机退款率：0.048433891164258905。\n",
      "年月：208004，随机退款率：0.03875293387910571。\n",
      "年月：208005，随机退款率：0.05803989362429779。\n"
     ]
    }
   ],
   "source": [
    "refund_index = []\n",
    "for each_ym,each_refund_rate in [*zip(ym,refund_rate)]: \n",
    "    print(\"年月：{}，随机退款率：{}。\".format(each_ym,each_refund_rate))\n",
    "    each_ym_index = sales[sales[\"ym\"] == each_ym].index\n",
    "    rs = np.random.RandomState(each_ym)\n",
    "    refund_index += list(rs.choice(each_ym_index,size=int(len(each_ym_index)*each_refund_rate)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "fcbc5d4a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "总体退款率： 0.046882657228423635\n"
     ]
    }
   ],
   "source": [
    "print(\"总体退款率：\",len(sales.loc[refund_index]) / len(sales))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "c8a311a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "refund = sales.loc[refund_index,[\"订单ID\",\"用户ID\",\"购买复购\",\"付款时间\",\"实付款\",\"课时数\"]].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "56fe951a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 课时剩余率\n",
    "rs = np.random.RandomState(year*11)\n",
    "refund_course_rate = [i if i <1 else 0.9 for i in rs.normal(0.9,0.1,len(refund))] \n",
    "\n",
    "refund[\"剩余课时数\"] = (refund[\"课时数\"] * refund_course_rate).astype(int)\n",
    "\n",
    "refund[\"退款金额\"] = ((refund['剩余课时数'] / refund['课时数']) * refund['实付款']).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "f803ecfe",
   "metadata": {},
   "outputs": [],
   "source": [
    "rs = np.random.RandomState(year*12)\n",
    "randint = np.around(rs.gamma(5,1,len(refund)))\n",
    "refund[\"退款时间\"] = refund[\"付款时间\"] + pd.to_timedelta(randint.astype('int32'),unit=\"d\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "7dcc696d",
   "metadata": {},
   "outputs": [],
   "source": [
    "rs = np.random.RandomState(year*13)\n",
    "refund[\"退款原因\"] = rs.choice(cate_refund[\"退款原因\"],refund.shape[0],p=cate_refund[\"P\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92b73928",
   "metadata": {},
   "source": [
    "# 导出数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "35dce1c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "user.to_excel(\"user.xlsx\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "293c3c10",
   "metadata": {},
   "outputs": [],
   "source": [
    "sales.to_excel(\"sales.xlsx\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "6ad65e3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "refund.to_excel(\"refund.xlsx\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "df7b4227",
   "metadata": {},
   "outputs": [],
   "source": [
    "behaviors.drop(columns=[\"ym\",\"行为时间\"]).to_csv('behaviors.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "b631e6df",
   "metadata": {},
   "outputs": [],
   "source": [
    "ABTEST.to_excel(\"ABTEST.xlsx\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "c082171f",
   "metadata": {},
   "outputs": [],
   "source": [
    "ad.to_excel(\"ad.xlsx\",index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  },
  "toc-autonumbering": true,
  "toc-showmarkdowntxt": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
